package com.hmkj.core.mapper.order;

import com.hmkj.common.baseMapper.GenericMapper;
import com.hmkj.core.model.order.OrderModel;
import com.hmkj.core.po.order.Order;
import com.hmkj.core.po.system.Admin;
import io.swagger.models.auth.In;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 用户点买策略订单相关Dao接口<br>
 *
 * @author lizhijie
 * @date 2018-07-05 05:19:00
 */
public interface OrderMapper extends GenericMapper<Order, Integer> {

    /**
     * 投资人列表
     *
     * @param params
     * @return
     */
    @Select("SELECT t.*,u.user_name AS 'u.userName',u.mobile_phone AS 'u.mobilePhone',i.user_name AS 'i.userName'  FROM biz_order t LEFT JOIN biz_user u ON u.id = t.user_id LEFT JOIN biz_user i on  i.id = t.investor_id  WHERE 1 = 1 ${whereSql} ${sortSql} limit ${nowPage}, ${pageSize}")
    List<OrderModel> findListBySqlJoin(Map<String, Object> params);

    /**
     * 用户统计
     *
     * @param params
     * @return
     */
    @Select("SELECT count(1) FROM biz_order t LEFT JOIN biz_user u ON u.id = t.user_id LEFT JOIN biz_user i on  i.id = t.investor_id WHERE 1 = 1 ${whereSql}")
    long countBySqlJoin(Map<String, Object> params);

    /**
     * 询某一时间段的结算订单
     *
     * @param param
     * @return
     */
    @Select(" <script> SELECT t.* " +
            "FROM biz_order t " +
            "WHERE t.user_id = #{userId} " +
            "<if test=\"date != null and date == 1\"> AND <![CDATA[ to_days(t.create_time) = to_days(now()) ]]></if>" +
            "<if test=\"date != null and date == 7\"> AND <![CDATA[ DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(t.create_time) ]]></if>" +
            "<if test=\"date != null and date == 30\"> AND <![CDATA[ DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= date(t.create_time) ]]></if>" +
            "<if test=\"date != null and date == 90\"> AND <![CDATA[ DATE_SUB(CURDATE(), INTERVAL INTERVAL 3 MONTH) <= date(t.create_time) ]]></if>" +
            "<if test=\"date != null and date == 365\"> AND <![CDATA[ DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 YEAR) <= date(t.create_time) ]]></if>" +
            "<if test=\"status != null and status != ''\"> AND t.status = #{status}  </if>" +
            "AND t.del_flag = 1 " +
            "ORDER BY t.status asc ,t.settle_time desc</script>")
    List<Order> findOrderForTime(Map<String, Object> param);

    /**
     * 根据订单号查询
     *
     * @param orderNo
     * @return
     */
    @Select("SELECT t.* FROM biz_order t WHERE 1 = 1 and t.order_no = #{orderNo}")
    Order selectByOrderNo(@Param(value = "orderNo") String orderNo);

    /**
     * 点卖列表
     *
     * @param param 参数集合
     * @return List<Order>
     */
    @Select(" <script> SELECT t.* " +
            "FROM biz_order t " +
            "WHERE t.user_id = #{userId} " +
            "<if test=\"type != null and type == 1\"> AND t.status IN (1,2) </if>" +
            "<if test=\"type != null and type == 2\"> AND t.status IN (3,4,5) </if>" +
            "<if test=\"type != null and type == 3\"> AND t.status IN (6,7,8) </if>" +
            "AND t.del_flag = 1 " +
            "ORDER BY t.status ASC, t.id desc </script>")
    List<OrderModel> findListForOrder(Map<String, Object> param);

    /**
     * 点卖列表
     *
     * @param param 参数集合
     * @return Integer
     */
    @Select(" <script> SELECT count(*) " +
            "FROM biz_order t " +
            "WHERE t.user_id = #{userId} " +
            "<if test=\"type != null and type == 1\"> AND t.status IN (1,2) </if>" +
            "<if test=\"type != null and type == 2\"> AND t.status IN (3,4,5) </if>" +
            "<if test=\"type != null and type == 3\"> AND t.status IN (6,7,8) </if>" +
            "AND t.del_flag = 1 " +
            "ORDER BY t.status ASC, t.id desc </script> ")
    Integer findCountForOrder(Map<String, Object> param);

    /**
     *
     * @param userIds
     * @return
     */
    @Select("SELECT t.* FROM biz_order t WHERE t.user_id in (#{userIds}) AND t.buy_time >= (#{startTime}) AND t.buy_time <= (#{endTime}) AND t.status <8 AND t.status >1 AND t.delFlag = 1")
    List<Order> findOrderByUser(@Param(value="userIds") String userIds,
                                @Param(value="startTime") Date startTime,
                                @Param(value="endTime") Date endTime);

    @Select("SELECT o.* FROM Order o WHERE o.user_id in (#{userIds}) AND o.settle_time >= (#{startTime}) AND o.settle_time <= (#{endTime}) AND o.status =  AND o.del_flag = 1")
    List<Order> findUserOrder(
            @Param(value="userIds") String userIds,
            @Param(value="startTime") Date startTime,
            @Param(value="endTime") Date endTime);

    @Select("SELECT userId,SUM(trade_credit) AS sumTradeCredit,SUM(unpaid_diyan) AS sumUnpaidDiYan FROM biz_order WHERE DATE(buy_time) != CURRENT_DATE AND status = #{status} AND del_flag= 1 GROUP BY user_id ")
    List<OrderModel> findDiYanGroupByUser(@Param(value = "status")Integer status);

    @Select("SELECT count(*) FROM (SELECT count(1) FROM biz_order t LEFT JOIN biz_user u ON t.user_id = u.id  WHERE DATE(t.buy_time) != CURRENT_DATE AND t.del_flag= 1 ${whereSql} ${sortSql} GROUP BY t.user_id) a ")
    Integer findCountDtGridDiYanGroupByUser(Map<String, Object> param);

    @Select("SELECT t.user_id,u.user_name AS 'u.userName', u.mobile_phone AS 'u.mobilePhone', SUM(t.trade_credit) AS sumTradeCredit,SUM(t.unpaid_diyan) AS sumUnpaidDiYan FROM biz_order t LEFT JOIN biz_user u ON t.user_id = u.id  WHERE DATE(t.buy_time) != CURRENT_DATE AND t.del_flag= 1 ${whereSql} ${sortSql} GROUP BY t.user_id ")
    List<OrderModel> findListDtGridDiYanGroupByUser(Map<String, Object> param);

    @Select("SELECT * FROM biz_order WHERE del_flag = 1 AND DATE_FORMAT(buy_deal_number,'%m-%d-%Y') = DATE_FORMAT(NOW(),'%m-%d-%Y') ")
    List<Order> selectTodayDealOrder();

    @Select("SELECT * FROM biz_order WHERE del_flag = 1 AND status = #{status} AND DATE_FORMAT(buy_deal_number,'%m-%d-%Y') = DATE_FORMAT(NOW(),'%m-%d-%Y') ")
    List<Order> selectTodaySettleOrder(@Param(value="status") Integer status);

    @Select("SELECT * FROM biz_order t WHERE t.del_flag = 1 AND t.user_id in (#{userIds}) AND  t.buy_time >= #{startTime} AND t.buy_time <= #{endTime} AND  t.status = #{status}")
    List<Order> selectOrderWithTime1(Map<String, Object> param);

    @Select("SELECT * FROM biz_order t WHERE  t.del_flag = 1 AND t.user_id in (#{userIds}) AND  t.buy_time >= #{startTime} AND t.buy_time <= #{endTime} AND  t.status > 1 AND t.status < 8 ")
    List<Order> selectOrderWithTime2(Map<String, Object> param);

    @Select("SELECT * FROM biz_order t WHERE t.del_flag = 1 AND  t.buy_time >= #{startTime} AND t.buy_time <= #{endTime} AND  t.status = #{status}")
    List<Order> selectOrderAllWithTime(Map<String, Object> param);

    @Select("SELECT * FROM biz_order t WHERE  t.del_flag = 1 AND t.buy_time >= #{startTime} AND t.buy_time <= #{endTime} AND  t.status > 1 AND t.status < 8 ")
    List<Order> selectOrderAllWithTime2(Map<String, Object> param);

    /**
     * 查询昨天策略交易笔数
     * @param
     * @return
     */
    @Select("SELECT t.* FROM biz_order t WHERE t.user_id = #{userId} AND to_days(now()) - to_days(t.create_time) <= 1 AND t.status = 7 AND t.del_flag = 1 ")
    List<Order> selectYesStrategy(@Param(value="userId") Integer userId);

    /**
     * 查询策略发布最后时间
     * @param userId
     * @return
     */
    @Select("SELECT t.create_time FROM biz_order t WHERE t.user_id = #{userId} AND t.status = 7 AND t.del_flag = 1 ORDER BY t.create_time desc limit 1")
    Order selectByOne(@Param(value="userId") Integer userId);
}
